'''
该模块包含数据库存取相关的函数
数据库使用sqlite
'''
import sqlite3

#数据库位置
CONNECTION_STR='CanteenData.db3'
#餐品表名
DISHTABLE_STR='dishInfo'


def print_dish():
    """打印餐品表数据"""
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        cursor=c.execute("SELECT ID,NAME,TYPE,PRICE,MONEYCOST,TIMECOST,STAR FROM dishInfo")
        for row in cursor:
            print(row)

def get_dish():
    """返回餐品表数据
    data_rows:包含餐品数据表中所有的行的列表(list)，每个行以元祖(tuple)形式存储
    每行包括的数据为 [ID(int),NAME(text),TYPE(text),PRICE(real),MONEYCOST(real),TIMECOST(real),STAR(real)]
    含义为 ['序号','名称','类型','价格','平均制作成本','平均制作时间','评价']
    """
    data_rows=[]
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        cursor=c.execute("SELECT ID,NAME,TYPE,PRICE,MONEYCOST,TIMECOST,STAR FROM dishInfo")
        for row in cursor:
            data_rows.append(row)
    return data_rows

def get_dish_info(name,col_name):
    """从名称返回餐品表个别数据
    """
    col_value=""
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        cursor=c.execute("SELECT {0} FROM dishInfo WHERE NAME='{1}'".format(col_name,name))
        for row in cursor:
            col_value=row[0]
    return col_value

def get_dish_contain(col_name,value):
    """判断某行的某个字符串值是否存在"""
    is_contain=False
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        cursor=c.execute("SELECT * FROM dishInfo WHERE {0}='{1}'".format(col_name,value))
        for row in cursor:
            if len(row)>1:
                is_contain=True
    return is_contain

def add_dish(id,name,type,price,money_cost,time_cost,star='3'):
    """添加餐品数据,id为主键，不能使用重复值s
    数据格式为[id(int),name(str),type(str),price(float),money_cost(float),tiem_cost(float),star(float)]
    数据含义为['序号','名称','类型','价格','平均制作成本','平均制作时间','评价'] 
    """
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        try:
            c.execute(
                "INSERT INTO dishInfo VALUES({0},'{1}','{2}',{3},{4},{5},{6})".format(
                    id,name,type,price,money_cost,time_cost,star
                    )
                )
            conn.commit()
        except Exception as e:
            print("添加餐品数据发生{0}错误,可能是使用了重复的主键值,或格式不正确".format(e))
        else:
            print("成功添加菜品{0}".format(name))

def update_dish(id,name,type,price,money_cost,time_cost,star='3'):
    """修改主键为id的餐品数据
    数据格式为[id(int),name(str),type(str),price(float),money_cost(float),tiem_cost(float),star(float)]
    数据含义为['序号','名称','类型','价格','平均制作成本','平均制作时间','评价'] 
    """
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        try:
            c.execute(
                "UPDATE dishInfo SET NAME='{1}',TYPE='{2}',PRICE={3},MONEYCOST={4},TIMECOST={5},STAR={6} WHERE ID={0}".format(
                    id,name,type,price,money_cost,time_cost,star
                    )
                )
            conn.commit()
        except Exception as e:
            print("修改餐品数据发生{0}错误,可能是输入的格式不正确".format(e))
        else:
            print("成功修改了菜品{0}的数据".format(name))

def remove_dish(id):
    """根据id删除表中的数据"""
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        try:
            c.execute("DELETE FROM dishInfo WHERE ID={0}".format(id))
            conn.commit()
        except Exception as e:
            print("删除餐品数据发生{0}错误,可能输入主键值不存在,或格式不正确".format(e))
        else:
            print("成功删除序号为{0}的菜品".format(id))
            
def get_user(root=True):
    """返回用户表数据
    @root:布尔型，是否是主管
    """
    data_rows=[]
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        if(root):
            cursor=c.execute("SELECT ID,NAME,PASSWORD,CATEGORY,PHONE,REGTIME FROM userInfo")
        else:
            cursor=c.execute("SELECT ID,NAME,PASSWORD,CATEGORY,PHONE,REGTIME FROM userInfo WHERE NOT (CATEGORY='管理员' OR CATEGORY='主管')")
        for row in cursor:
            data_rows.append(row)
    return data_rows

def add_user(id,name,password,category,phone,regtime):
    """添加用户数据
    """
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        try:
            c.execute(
                "INSERT INTO userInfo VALUES({0},'{1}','{2}','{3}','{4}','{5}')".format(
                    id,name,password,category,phone,regtime
                    )
                )
            conn.commit()
        except Exception as e:
            print("添加用户数据发生{0}错误,可能是使用了重复的主键/名称值,或格式不正确".format(e))
        else:
            print("成功添加名为{0}的用户".format(name))

def update_user(id,name,password,category,phone,regtime):
    """修改主键为id的用户数据
    """
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        try:
            c.execute(
                "UPDATE userInfo SET NAME='{1}',PASSWORD='{2}',CATEGORY='{3}',PHONE='{4}',REGTIME='{5}'WHERE ID={0}".format(
                    id,name,password,category,phone,regtime
                    )
                )
            conn.commit()
        except Exception as e:
            print("修改用户数据发生{0}错误,可能是输入的格式不正确".format(e))
        else:
            print("成功修改了用户{0}的数据".format(name))

def remove_user(id):
    """根据id删除表中的数据"""
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        try:
            c.execute("DELETE FROM userInfo WHERE ID={0}".format(id))
            conn.commit()
        except Exception as e:
            print("删除用户数据发生{0}错误,可能输入主键值不存在,或格式不正确".format(e))
        else:
            print("成功删除序号{0}的用户".format(id))

def get_user_contain(col_name,value):
    """判断某行的某个字符串值是否存在"""
    is_contain=False
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        cursor=c.execute("SELECT * FROM userInfo WHERE {0}='{1}'".format(col_name,value))
        for row in cursor:
            if len(row)>1:
                is_contain=True
    return is_contain

def user_next_id():
    """获取下一位用户的id"""
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        cursor=c.execute("SELECT ID FROM userInfo")
        count=0
        for row in cursor:
            count+=1
        return count

def get_user_id(name,password):
    """通过用户名和密码获取id"""
    id=""
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        cursor=c.execute("SELECT ID FROM userInfo WHERE NAME='{0}' AND PASSWORD='{1}'".format(name,password))
        for row in cursor:
            id=row[0]
    return str(id)    

def get_user_regtime(id):
    """获取id对应的注册时间"""
    time=""
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        cursor=c.execute("SELECT REGTIME FROM userInfo WHERE ID={0}".format(id))
        for row in cursor:
            time=row[0]
    return time

def get_user_category(id):
    """获取id对应的身份"""
    category=""
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        cursor=c.execute("SELECT CATEGORY FROM userInfo WHERE ID={0}".format(id))
        for row in cursor:
            category=row[0]
    return category

def name2id(name):
    """将食物名称转换为id
    """
    id=0
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        cursor=c.execute("SELECT ID FROM dishInfo WHERE NAME='{0}'".format(name))
        for row in cursor:
            id=row[0];
    return id

def get_order():
    """获取订单信息"""
    data_rows=[]
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        cursor=c.execute("SELECT ID,DISHID,DISHNAME,PRICE,TABLEID,AMOUNT,STATE,COOKID FROM orderInfo")
        for row in cursor:
            data_rows.append(row)
    return data_rows

def add_order(id,dish_id,dish_name,price,table_id,amount,state,cook_id):
    """添加订单数据,id为主键，不能使用重复值
    """
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        try:
            c.execute(
                "INSERT INTO orderInfo VALUES({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}')".format(
                    id,dish_id,dish_name,price,table_id,amount,state,cook_id
                    )
                )
            conn.commit()
        except Exception as e:
            print("添加订单数据发生{0}错误,可能是使用了重复的主键值,或格式不正确".format(e))
        else:
            print("成功添加序号{0}的订单".format(id))

def update_order_state(id,state,cook_id):
    """更新订单状态"""
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        try:
            c.execute(
                "UPDATE orderInfo SET STATE='{1}',COOKID='{2}' WHERE ID={0}".format(id,state,cook_id)
                )
            conn.commit()
        except Exception as e:
            print("修改订单数据发生{0}错误".format(e))

def get_order_info(id,col_name):
    """获取订单的某列信息"""
    info=""
    with sqlite3.connect(CONNECTION_STR) as conn:
        c=conn.cursor()
        cursor=c.execute("SELECT {0} FROM orderInfo WHERE ID={1}".format(col_name,id))
        for row in cursor:
            info=row[0]
    return str(info)